package com.iteye.trafficRecorder;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String name = android.os.Environment.getExternalStorageDirectory() + "/testdb/traffic_recorder.sqlite";
    //private static final String name = "traffic_recorder.sqlite"; //数据库名称
    
    private static final int version = 1; //数据库版本

	public DatabaseHelper(Context context) {
		super(context, name, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS user (user_id integer primary key, guid text, car_number text, user_name text, phone_number text, device_type text, device_info text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS setting (setting_id integer primary key, ws_address text, gps_timer text, store_local text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS location (location_id text primary key, time text, phoneNum text, longitude text, latitude text, altitude text, provider text, satelliteCount text, speed text, temperature text, direction text, distance text, info text)");
		//db.execSQL("insert into setting(setting_id, ws_address, gps_timer, store_local) values(?,?,?,?)", new Object[]{1, Setting.getWebServiceAddress(), Setting.getGpsRecordsecond(), (Setting.isStoreLocal()?1:0)});
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
	
	private SQLiteDatabase db;
	
	public void openDb() {
		if (db == null) {
			db = getWritableDatabase();
		}
	}
	
	public void closeDb() {
		if (db != null) {
			db.close();
			db = null;
		}
	}
	
	public void registUser(User user) {
		
	}
	
	public User getUser() {
        Cursor cursor = db.rawQuery("select car_number, user_name, phone_number, device_type, device_info, guid from user where user_id=1", null);
        if (cursor.moveToNext()) {
        	User user = new User();
        	user.setCarNumber(cursor.getString(0));
        	user.setUserName(cursor.getString(1));
        	user.setPhoneNumber(cursor.getString(2));
        	user.setDeviceType(cursor.getString(3));
        	user.setDeviceInfo(cursor.getString(4));
        	user.setGuid(cursor.getString(5));
        	return user;
        }
        return null;
	}
	
	public void insertUser(User user) {
		db.execSQL("insert into user(user_id, car_number, user_name, phone_number, device_type, device_info) values(?,?,?,?,?,?)", 
				new Object[]{1, user.getCarNumber(), user.getUserName(), user.getPhoneNumber(), user.getDeviceType(), user.getDeviceInfo()});
	}
	
	public void updateUser(User user) {
		db.execSQL("update user set car_number=?, user_name=?, phone_number=?, device_type=?, device_info=? where user_id=1", 
				new Object[]{user.getCarNumber(), user.getUserName(), user.getPhoneNumber(), user.getDeviceType(), user.getDeviceInfo()});
	}
	
	/*
	public void loadSetting() {
        Cursor cursor = db.rawQuery("select ws_address, gps_timer, store_local from setting where setting_id=1", null);
        if (cursor.moveToNext()) {
        	Setting.setWebServiceAddress(cursor.getString(0));
        	Setting.setGpsRecordsecond(cursor.getInt(1));
        	Setting.setStoreLocal(cursor.getInt(2) == 1);
        }
	}
	
	public void saveSetting() {
		db.execSQL("update setting set ws_address=?, gps_timer=?, store_local=? where setting_id=1", new Object[]{Setting.getWebServiceAddress(), Setting.getGpsRecordsecond(), (Setting.isStoreLocal()?1:0)});
	}
	*/
	
	public void insertLocation(MyLocation location) {
		if (db == null || location == null) {
			return;
		}
		db.execSQL("insert into location(location_id, phoneNum, time, longitude, latitude, altitude, provider, satelliteCount, speed, temperature, direction, distance) values(?,?,?,?,?,?,?,?,?,?,?,?)", 
				new Object[]{location.getLocationId(), location.getPhoneNum(), location.getTime(), location.getLongitude(), location.getLatitude(), location.getAltitude(), location.getProvider(), 
				location.getSatelliteCount(), location.getSpeed(), location.getTemperature(), location.getDirection(), location.getDistance()});
	}
	
	public List<MyLocation> getLocations() {
		List<MyLocation> locations = new ArrayList<MyLocation>();
        Cursor cursor = db.rawQuery("select location_id, phoneNum, time, longitude, latitude, altitude, provider, satelliteCount, speed, temperature, direction, distance from location", null);
        while (cursor.moveToNext()) {
        	MyLocation location = new MyLocation();
        	location.setLocationId(cursor.getString(0));
        	location.setPhoneNum(cursor.getString(1));
        	location.setTime(cursor.getLong(2));
        	location.setLongitude(cursor.getDouble(3));
        	location.setLatitude(cursor.getDouble(4));
        	location.setAltitude(cursor.getDouble(5));
        	location.setProvider(cursor.getString(6));
        	location.setSatelliteCount(cursor.getInt(7));
        	location.setSpeed(cursor.getFloat(8));
        	location.setTemperature(cursor.getFloat(9));
        	location.setDirection(cursor.getFloat(10));
        	location.setDistance(cursor.getFloat(11));
        	locations.add(location);
        }
		return locations;
	}
	
	public void deleteLocation(String locationId) {
		db.execSQL("delete from location where location_id=?", new Object[]{locationId});
	}
}

